﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VRDD.OutputDto._20220314;
using HCQ2_Model.VRQDMeta.InputDto._20220418;
using HCQ2_Model.VRQDMeta.OutputDto._20220418;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDVideoDynamicDAL: IT_VRDDVideoDynamicDAL
    {
        /// <summary>
        ///  获取我的视频动态列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="create_id">视频创建人</param>
        /// <param name="user_id">视频点赞人</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<MyVideoNewsOutput> GetMyVideoNews(MyVideoNewsInput param,int create_id,int user_id,out int total)
        {
            sb?.Clear();
            sb.Append("SELECT d.*,u.*,i.*,ROW_NUMBER() OVER(ORDER BY d.createTime DESC)AS num FROM ( ");
            string dateStart = !string.IsNullOrEmpty(param.dateStart) ? DateTime.ParseExact(param.dateStart, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString() : "",
                dateEnd = !string.IsNullOrEmpty(param.dateEnd) ? DateTime.ParseExact(param.dateEnd, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString() : "";
            //未下架
            StringBuilder tdType1 = new StringBuilder();
            tdType1.Append($@" SELECT CAST(0 AS BIT) AS isTakeDown,newsID,fileType AS type,visitType,video,image,fileID,note,userid,gpsX,gpsY,
            locationName,CAST((SELECT COUNT(0) FROM dbo.T_VRDDUserVideoDynamicStar WHERE userid='{param.userid}'
            AND newsID=dc.newsID) AS BIT)AS isStar,starNum,sharedNum,
            CONVERT(varchar(100),create_date,120) AS createTime FROM dbo.T_VRDDVideoDynamic dc 
            WHERE userid='{param.createid}' AND newsStatus=0 ");
            if (!string.IsNullOrEmpty(dateStart))
                tdType1.Append($@" AND create_date>={dateStart}");
            if (!string.IsNullOrEmpty(dateEnd))
                tdType1.Append($@" AND create_date<={dateEnd}");
            //已下架
            StringBuilder tdType2 = new StringBuilder();
            tdType2.Append($@"SELECT CAST(1 AS BIT) AS isTakeDown,newsID,fileType AS type,visitType,video,image,fileID,note,userid,gpsX,gpsY,
            locationName,CAST((SELECT COUNT(0) FROM dbo.T_VRDDUserVideoDynamicStar WHERE userid='{param.userid}'
            AND newsID=dc.newsID) AS BIT)AS isStar,starNum,sharedNum,
            CONVERT(varchar(100),create_date,120) AS createTime FROM dbo.T_VRDDVideoDynamic dc 
            WHERE userid='{param.createid}' AND (newsStatus=1 OR fileID IN(
	            --过期房间
	            SELECT file_id FROM dbo.T_DocumentInfo WHERE create_id={create_id} AND page_type='QDMetaRoom' AND game_end<GETDATE()
                --下架应用
				UNION
				SELECT file_id FROM dbo.T_DocumentInfo WHERE create_id={create_id} AND page_type='QDMetaProgram' AND file_status=4
	            --下架应用的房间
	            UNION
	            SELECT file_id FROM dbo.T_DocumentInfo WHERE programId 
	            IN(SELECT file_id FROM dbo.T_DocumentInfo WHERE create_id={create_id} AND page_type='QDMetaProgram' AND file_status=4)
            )) ");
            if (!string.IsNullOrEmpty(dateStart))
                tdType2.Append($@" AND create_date>={dateStart}");
            if (!string.IsNullOrEmpty(dateEnd))
                tdType2.Append($@" AND create_date<={dateEnd}");
            switch (param.tdType)
            {
                case 0: {
                        //未下架
                        sb.Append(tdType1.ToString());
                    } break;
                case 1: {
                        //已下架
                        sb.Append(tdType2.ToString());
                    } break;
                default: {
                        //全部
                        sb.Append($"{tdType1} UNION {tdType2}");
                    } break;
            }
            sb.Append($@")d INNER JOIN 
            (SELECT user_guid as ownerID,user_id,user_name AS ownerName,user_img AS 
            ownerImage FROM dbo.T_User WHERE user_guid='{param.createid}')u ON d.userid=u.ownerID
            LEFT JOIN (SELECT file_id,file_name AS fileName FROM dbo.T_DocumentInfo)i ON d.fileID=i.file_id)dy ");
            total = SqlHelper.ExecuteScalarInt32($"select count(0) from ( {sb.ToString()}");
            return SqlHelper.ExecuteList<MyVideoNewsOutput>($"select top {param.rows} * from( {sb} where dy.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取动态视频年月
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<List<VideoNewsDateModel>> GetVideoNewsDateList(VideoNewsDateInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM (
            SELECT DISTINCT YEAR(create_date)AS year,MONTH(create_date)AS month
            FROM dbo.T_VRDDVideoDynamic WHERE userid='{param.userid}' ");
            if (param.tdType > -1)
                sb.Append($" AND newsStatus={param.tdType} ");
            sb.Append(")i ORDER BY i.year,i.month;");
            return await Task.Run(() => SqlHelper.ExecuteList<VideoNewsDateModel>(sb.ToString()));
        }
        /// <summary>
        ///  获取视频动态列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<VideoNewsListOutput> GetVideoNews(VideoNewsListInput param, int user_id, out int total)
        {
            sb?.Clear();
            sb.Append($@"SELECT *,ROW_NUMBER() OVER(ORDER BY t.createTime DESC)AS num FROM (
                SELECT CASE WHEN d.visitType=0 AND d.userid='{param.userid}' THEN 1
                WHEN d.visitType=1 AND u.isFriend>0 THEN 1
                WHEN d.visitType=1 AND d.userid='{param.userid}' THEN 1
                WHEN d.visitType=2 THEN 1
                ELSE 0 END AS isShow,* FROM 
                (SELECT newsID,fileType AS type,visitType,video,image,fileID,note,userid,gpsX,gpsY,
                locationName,CAST((SELECT COUNT(0) FROM dbo.T_VRDDUserVideoDynamicStar WHERE userid='{param.userid}'
                AND newsID=dc.newsID) AS BIT)AS isStar,starNum,sharedNum,
                CONVERT(varchar(100),create_date,120) AS createTime FROM dbo.T_VRDDVideoDynamic dc 
                WHERE 1=1 ");
            if (param.tdType > -1)
                sb.Append($" AND newsStatus={param.tdType} ");
            sb.Append($@")d  INNER JOIN 
                (SELECT user_guid as ownerID,user_id,user_name AS ownerName,user_img AS 
                ownerImage,isFriend=(SELECT COUNT(0) FROM dbo.DD_1 WHERE ((dd_id_1=s.user_id AND dd_id_2={user_id}) OR
                (dd_id_1={user_id} AND dd_id_2=s.user_id))) FROM dbo.T_User s)u ON d.userid=u.ownerID
                LEFT JOIN (SELECT file_id,file_name AS fileName FROM dbo.T_DocumentInfo)i ON d.fileID=i.file_id
                )t WHERE t.isShow=1)dy ");
            total = SqlHelper.ExecuteScalarInt32($"select count(0) from ( {sb.ToString()}");
            return SqlHelper.ExecuteList<VideoNewsListOutput>($"select top {param.rows} * from( {sb.ToString()} where dy.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取房间商品详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public MetaRoomDetailOutput GetMetaRoomDetail(MetaRoomDetailInput param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM 
            (SELECT doc_number as verNumber,  file_id AS fileID,file_name AS fileName,programId,file_note AS fileNote,play_num AS onlineNum,
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id={param.fileID} AND create_id={user_id}),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id={param.fileID} AND user_id={user_id}),
            isPaid=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.N_OrderCommodityInfo WHERE file_id={param.fileID} AND OrderCode IN(
	            SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={user_id} AND OrderState IN(2,4))),
            file_code AS fileCode,file_order AS fileOrder,qr_code AS roomPwd,file_money AS filePrice,
            pag_num AS playerNum,CONVERT(varchar(100),game_start,120) AS gameStart,vr_star AS starNum,
            CONVERT(varchar(100),game_end,120) AS gameEnd,browse_num AS browseNum,hold_num AS holdNum,
            pay_num AS payNum,CONVERT(varchar(100),update_time,120) AS updateTime,update_name AS updateName,
            CONVERT(varchar(100),create_time,120) AS createTime,create_name AS createName,create_id AS createID,image_url AS imageUrl,
            createImage=(SELECT user_img FROM dbo.T_User WHERE user_id=i.create_id) FROM dbo.T_DocumentInfo i
            WHERE file_id={param.fileID}) room INNER JOIN
            (SELECT file_id AS appFileID,file_name AS appFileName,attach_url AS attachUrl,image_url AS appImage,attach_url AS appID,file_size AS appSize,
            down_number AS downNum
            FROM dbo.T_DocumentInfo)pro ON room.programId=pro.appFileID INNER JOIN
            (SELECT file_id,folder_id AS folderID,folderName=(SELECT folder_name FROM dbo.T_DocumentFolder
            WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n)rel ON pro.appFileID=rel.file_id;");
            return SqlHelper.ExecuteList<MetaRoomDetailOutput>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        ///  获取素材商品详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public MaterialDetailOutput GetMaterialDetail(MaterialDetailInput param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM 
            (SELECT file_id AS fileID,file_name AS fileName,file_note AS fileNote,mater_type AS fileFormat,
            file_code AS fileCode,file_order AS fileOrder,file_money AS filePrice,attach_url AS attachUrl,
            image_url AS imageUrl,file_size AS fileSize,CONVERT(varchar(100),issue_start,120) AS releaseTime,
            vr_star AS starNum,browse_num AS browseNum,hold_num AS holdNum,down_number AS downNum,file_status AS fileStatus, 
            pay_num AS payNum,CONVERT(varchar(100),update_time,120) AS updateTime,update_name AS updateName,
            CONVERT(varchar(100),create_time,120) AS createTime,create_name AS createName,create_id AS createID,
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id=i.file_id AND create_id={user_id}),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id=i.file_id AND user_id={user_id}),
            isPaid=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.N_OrderCommodityInfo WHERE file_id=i.file_id AND OrderCode IN(
	            SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={user_id} AND OrderState IN(2,4))),
            createImage=(SELECT user_img FROM dbo.T_User WHERE user_id=i.create_id) FROM dbo.T_DocumentInfo i
            WHERE file_id={param.fileID}) f INNER JOIN
            (SELECT file_id,folder_id AS folderID,folderName=(SELECT folder_name FROM dbo.T_DocumentFolder
            WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n)r ON f.fileID=r.file_id;");
            return SqlHelper.ExecuteList<MaterialDetailOutput>(sb.ToString())?.FirstOrDefault();
        }
    }
}